SELECT t.*,
  t_dictionary.code AS type_code,
  t_dictionary.name AS type_name
FROM c1_permission t
LEFT JOIN c1_dictionary t_dictionary
ON (t_dictionary.id = t.type_id)
WHERE T.SITE_ID = :siteId
AND EXISTS
  (SELECT 1
  FROM c1_role_permission e
  WHERE e.permission_id         = t.id
  AND instr(:roleIds, e.role_id)>0
  )
ORDER BY t.type_id,
  t.order_num,
  LENGTH(t.code) DESC
